Release notes

Issue: When running the Current Year Budget Detail query (AQ11), the system populates it from the prior year table (BUD2018_Detail, for example). Since the field definition row contains "RecordID" in cell B5, the existing RecordID for each record is pulled into column B from the prior year table. If a user inserts a New Detail Row at the bottom of the Detail block, the Add Detail calc method will assign the next sequential RecordID based on the number of detail records above within this block. This may create a duplicate RecordID, if that value assigned already exists in the rows above (from the prior year table).

Resolution: This issue mainly occurred if a prior year record was set to zero after initially populating the row. Upon interface the following year, the zero record was not returned, and this caused the sequential numbering sequence to be out of sync. Updated the RECORDID logic so this does not occur.

Issue: When using "Salary" as the primary ProviderComp model, it appears that the Projected Compensation for the​ current year is either over or understated pending on how much of an FTE is involved as the YTD portion of the calculation​ does not take this into account.

Resolution: Corrected by updating how the YTD value is calculated to match the same logic use in Remaining Projection.

Issue: In the ProviderComp tab, the Insert PayType calc method includes a formula (in the calc method library) that references a cell that is always blank.

Resolution: Corrected by adjusting the formula in column DB to reference the Base Salary line 19 (column HM).

Issue: "New provider location defaults" in the Provider Configuration driver is hard coded to 'NA' in the calc method, which requires users to manually change it any time this driver is opened/refreshed.

Resolution: Corrected by updating cell V62 to be =AO62 so it will return any edits when refreshed. If blank, will return default of NA.

Issue: On row 26 of the 30-line Jobcode Block calc method on the JobCode tab, the formula results in #DIV/0 in columns BH-BS (FICA).

Resolution: Corrected by updating the FICA logic to error trap for instances where the department primary statistic is zero.

Issue: The employee name cell (column E) in the Employee calc method is formatted as Calibri 10. The other cells are Segui 10. The difference in font makes it more difficult for users to make out the employee name.

Resolution: Corrected by changing the font color and font type to Segoe UI 10.

Issue: When using ProviderDetail, the Amt Adjust column is missing the formula to acquire unit changes from the​GlobalVolChg driver, if configured.

Resolution: Corrected by updating the formula in column FN to reference the GlobalVolChg driver.

Issue: When inputting a percentage in the Admin Expense Adjustment driver, the note states "10=10%", but when looking at the plan file after making the adjustment, it appears that​ 10=1000%.

Resolution: Corrected by updating the formatting and how data is saved in the underlying table.

Issue: This driver is not ACCT.KHABgtCode complaint.

Resolution: Corrected the SumBy clause in AQ7 to refer to​ ACCT.KHABgtCode to be consistent/compliant with the data being grouped/brought into the plan files.

Issue: Currently in the DEPT dimensions table, when adding a new department, the default for ShowOnList_Budgeting is set to 1 (TRUE). This creates an issue during the budget cycle as these newly added departments (via costing or data imports) display on the budget list for users to select.

Resolution: Corrected by changing the default value for DEPT.ShowOnList_Budgeting field changed from 1 (TRUE) to 0 (FALSE).

Issue: Currently the EMPLOYEE_ROSTER_YYYY table does not include a SaveTagCustom column. This makes it very difficult to delete only certain records from the table when re-importing data.

Resolution: Corrected by adding SaveTagCustom to the 2020 Employee_Roster table.

Issue: In the Jobcode tab, column headers in cells EQ78:FB78 reference budget months in cells AG78:AR78 when in fact they should be formula driven by the rolling 12 actual history as defined in AQ7:Historical Hours Field Def row 17 (between columns EQ and FB).

Resolution: Corrected the category titles/labels to reflect the correct current actual year as well as the correct prior actual year. Also made formula changes to the JobCode tab in cell EQ147.

Issue: When printing the CDMRevenue tab, only key stat rows are included in the output. Non-key stat rows do not display.

Resolution: Corrected by changing the print range to include non key stats section for all print views.

Issue: The system displays a cell formula error in PROVIDER tab during the recalculation due to the Add New Encounter​ProviderDetail FinClass calc method.

Resolution: Corrected by updating formulas in column BD of the ProviderDetail sheet to contain the updated match logic.

Issue: When transferring FTEs from Axiom Capital Planning to Axiom Budgeting, the import utility needs to convert the FTEs​ to hours to be consistent with the Budgeting methodologies of​ storing hours and not FTEs.​

Resolution: Corrected by implementing several formula changes to the CP_Integration calc method to recognize the type as FTEs and convert to hours.

Issue: When using the capbud CYPMethod, those accounts where the YTD number exceeds the Current Year budget, the system appropriately projects zero for the rest of the year for all budget methodologies except for the Detail accounts. For those accounts, the system annualizes the rest of the year projection instead of projecting the rest of the year at zero.

Resolution: Corrected by adjusting the formula in line 1 of the Add New Detail and Detail calc methods.

Issue: When using the Revenue_Stat calc method and adding a budget group exception to the Revenue Adjustments driver, the percentage does not populate in column AQ of the Stat_Rev tab.

Resolution: Corrected by updating the Revenue_Stat calc method to accommodate budget group exceptions for Revenue Adjustments.

Issue: When using the GlobalProviderComp option for new providers on the ProviderComp tab, column P (total budget dollars) incorrectly includes a hard coded zero instead of the proper reference to BF for the current row.

Resolution: Corrected by identifying Add New Provider calc method line 10 (column HM) difference in cell references compared to related line items and adjusting formula column P.

Issue: When a user enters an input in the Manager Input, % Adjust, or Amt Adjust cells on the ProviderDetail tab for Misc_Provider, saves the sheet, and then collapses and expands the detail block, the inputs are not returned correctly. Re-saving the plan file can overwrite the input values.

Resolution: Corrected by adjusting the calc method library to store/retrieve data correctly when the block is collapsed and re-expanded.

Issue: When a user enters an input in the Manager Input, % Adjust, or Amt Adjust cells on the ProviderDetail tab for Misc_Provider, saves the sheet, and then collapses and expands the detail block, the system aligns the input incorrectly with the Medicine_Provider inputs. This issue does not occur if the user saves, closes, and then re-opens the file - only when they collapse and re-expand the detail block.

Resolution: Corrected by adjusting the calc method library to store/retrieve data correctly when the block is collapsed and re-expanded.

Issue: In the GlobalSum calc method of the Stat_Rev tab, the formulas in columns CL:CN incorrectly reference row 73.

Resolution: Corrected by updating the formulas to reference row 75, which contains text that matches the column names in the GlobalSum driver.

Issue: When a user adds a new employee to the Employee tab (Add New Employee calc method), the system does not reflect the merit increase amount from the Labor Rates driver and Empl_List sheet.

Resolution: Corrected by updating the formula in column CP.

Issue: The following issues were discovered for initiatives:

• When inserting a project initiative, the profile of accounts does not populate even though the Preview Query displays the listing.
• After the accounts populate, the Acct and Acct.Desc do not populate ​

Resolution: Corrected by updating the Control Sheet and adjusting the formula in the appropriate AQ.

Issue: When using the Employee Labor method and adding new employee(s) to a new block, the rate now propagates down to the FTE allocation section of the block and will not produce dollars. However, it will produce hours.

Resolution: Corrected by updating the formulas in column H of the Jobcode calc method.

Issue: When manually inserting a Jobcode, the Standard Hours look up to the Jobcode dimension uses the "Default" option even when configured for the "2080 Hours" option.

Resolution: AQ field def includes dimension columns for JobCode. If the record is changed, common workflow is to close and reopen the plan file. Former approach included GetDatas. To meet the demand of the common workflow, converted AQs to refresh on open along with conditional time stamp feature.

Issue: Prior to 2019.3 the client could enter any provider to ProviderComp regardless or Provider.BudgetGroup status. But in 2019.3 we added a BudgetGroup filter to AQ7: Provider List on the Drivers. This is a new AQ that brings Provider List data into the plan file for all providers matching the plan file budget group. However, when the user adds a provider to Provider Comp outside the plan file's budget group, the salary method is not correct and defaults to Salary incorrectly.

Resolution: DEPT.BudgetType is not connected to Provider.BudgetType. Due to the disconnect, Providers not connected to the single DEPT.BudgetType approach do not populate in Provider. The new approach brings in the same list of providers as ProviderComp into Drivers!AQ7. Data from the Budget_ProviderList driver is still updated when new providers are added.

Issue: When adding a new job code on the Jobcode tab, the dollars and hours flow over to the Expense tab, but the totals for FTEs, Hours, etc. at the top of the Jobcode tab (Summary section) do not update. This causes a mismatch between the Expense and Jobcode tab.

Resolution: Corrected by identifying ranges in the Summary section that required increase to row 181 to capture add new and totals.

Issue: When using the Jobcode tab and the Holiday calc method, the dollars are incorrectly calculated due to an incorrect column reference.

Resolution: Corrected by updating the Holiday calc method in columns AT:BE for the monthly dollars to lock the range reference to column M.

Issue: The Jobcode sheet does not include manager input for driver stats.​

Resolution: Corrected by reintroducing the manager input column from 2019.2.

Issue: The formatting of the blue box surrounding some columns displays intermittent white borders across rows.​

Resolution: Corrected by removing white borders on the Expense tab and Expense calc method rows.

Issue: When adding a row to the Dept Other Pay section, the system does not display calc methods to select. Users should expect to see the same available calc methods when adding a department in the Other Pay section of the other labor tabs.​

Resolution: Corrected by adding the following calc methods to labor sheets:​

• JobCode tab - Added New PayType calc method
• Employee tab - Added New Dept PayType calc method
• Staffing tab - Added New PayType calc method

Issue: The refresh variable option in calc method is not based on the driver setting.

Resolution: Corrected by updating refresh variable to point to the correct FINCLASS or LOCATION element.

Issue: Now that the twelfth month is editable, a situation can occur where a change in the amt adjustment may not tie to​ the sum of the twelve months if a user makes manual changes​ to the default formulas.​

Resolution: Corrected by updating the logic in the twelfth​ month to be read-only and used as the balancing month to​ annual total.

Issue: On the Employee tab, the EPTO row is hidden.

Resolution: Corrected by fixing formula reference error for hide logic.

Issue: This report includes the following issues:

• The report needs a refresh variable to filter by Budget Group or Entity
• Budget Interface to Labor worksheet only pulls in CY Actual YTD and CY Budget Total (Staffing is the only labor tab that uses LYA data).​
• Make various changes to AQ1-AQ3.​

Resolution: Corrected by making multiple changes to the Variables tab and other formula changes.

Issue: When a plan file includes Axiom Capital Planning integration data and the plan file is rebuilt, the system inserts duplicate integration records on the Expense tab

Resolution: Corrected by creating a new AQ to distinguish Axiom Capital Planning records in the BUDYYYY table by initiative ID.

Issue: PFB-08115 - Key Dept Ratios: Formula used in Salary Variance due to Pay Rates [TFS 40566]

Symptom: The formula for Salary Variance due to Pay Rates in the Key Dept Ratios report appears incorrect.

Resolution: Corrected by changing the formula on cell S68-Salary Variance Due to Pay rates. The total salary variance is now equal to the total of variances caused by rate, volume, efficiency.

Issue: PFB-08004 - Key Dept Ratios Volume Var formula error [TFS 40554]

Symptom: The revenue variance due to volume should be the same percentage as the variance percentage for Total Key Statistics. Issue was that Actual Revenue per unit was being used instead of Budget Revenue per unit.

Resolution: Corrected by doing the following:

• Adjusted the formula in Revenue Variance Due to Volume.
• Changed column K and P to use the budget per unit, not actual.
• Changed the formulas to use columns J and O in the formula.
• Changed the refresh variables type to use Grid instead ColumnValue.
• Modified the refresh variable relatedcolumnvalue to be RPTMap compliant.

Issue: PFB-06875 - Dept Monthly Package: Employee name is blank if the employee changed depts [TFS 29730]

Symptom: When a user has a fiscal year-end and they run this report if an employee changed departments, the employee​ name returns blank because the primary table is the current​ year and the employee ID does not show on that year but does​for the prior.

Resolution: Corrected by changing the logic to look at the current year. If the system does not find a name, it looks to the prior year. If both are blank, the system returns the message "Name Not Found".

Issue: PFB-07768 - Budget Variance by Dept - TotOpExp row Formatting [TFS 37083]

Symptom: The formatting of the row "Total Operating Expenses" should be consistent with the other subtotal rows.

Resolution: Corrected by formatting the Total Operating Expenses row to match the detail rows.

Issue: PFB-07904 - Dept Variance Roll Up has a typo for Outpatient revenue [TFS 37991]

Symptom: The word revenue is missing an 'n' in outpatient revenue in the Department Variance Roll up report located under Management Reporting\Report Packages\Executive\Dept Variance Rollup.

Resolution: Corrected by updating the label spelling.

Issue: PFB-07922 - Dept Monthly Package Total FTE Calc should be S46/(80*$R$5) [TFS 38229]

Symptom: The Total FTEs calculation on the EmpID sheet under the YTD column (column S) divides the total hours by 80. This should divide by 80 times number of pay periods.

Resolution: Corrected by updating the formula in cell S47 to include the number of pay periods.

Issue: PFB-08263 - Unable to save MultiDept Var due to incorrect logic in columns BD & BE [TFS 40833]

Symptom: When using the MultiDept Variance Report and summary accounts are employed, the system displays an error in column BF when the user saves the report.

Resolution: Corrected by adjusting the logic for YTD to always populate. This logic exists in columns BK and BL to populate field definition versions of YTD for LY or CY, otherwise for summary data, it utilizes GetData with the FSSummary filter.

Issue: BiWeekly to Monthly with LY.xlsx incorrectly treating the Paytype.Accrual=No paytypes [TFS 39835]

Symptom: The BiWeekly to Monthly with LY.xlsx incorrectly ignores the pay types where Paytype.Accrual=No. This filter should be Paytype.Accrual<>NA as a sheet filter. Where Paytype.Accural=No, the system should record the value in the month where the pay type is posted in the Payroll27 table.

Resolution: Corrected by updating the Sheet filter on Control Sheet in cell J66 to use Paytype.Accrue<>'NA' and to dynamically include Paytype.FTE='Yes' when selected for Hours.

Issue: Biweekly to Monthly incorrectly treating the Paytype.Accrual=No paytypes [TFS 39836]

Symptom: The BiWeekly to Monthly.xlsx incorrectly ignores the pay types where Paytype.Accrual=No. This filter should be Paytype.Accrual<>NA as a sheet filter. Where Paytype.Accural=No, the system should record the value in the month where the pay type is posted in the Payroll27 table.

Resolution: Corrected by updating the Sheet filter on Control Sheet in cell J66 to use Paytype.Accrue<>'NA' and to dynamically include Paytype.FTE='Yes' when selected for Hours.

Issue: PFB-07584 - Monthly to GL has new PAYTYPE.Provider column [TFS 35703]

Symptom: The report errors out upon open for invalid column PAYTYPE.Provider in non-provider licensed systems.

Resolution: Corrected cell AE4 to check for a Provider license. If the license is present, the system will use PayType.Provider. If the license does not exist, the cell remains blank with no field definition.

See PFB-06875

Symptom: In cell T6, the OR statement at the beginning of the formula means that if budget or target is zero, the result will always be zero. If a user does not have a target, even if there is budget, the result is always zero. The IF needs to go with each statement within the original IF statement.

Resolution: Corrected by updating the formula with an IF statement in cell T6.

Symptom: When using the ProductivityExec Review report to compare against the ProductivityVCC report for the Productivity Index, the two may not match because the ProductivityVCC report includes Paytype.PayDetail = 'Other' for Hours and Dollars while the ProductivityExec report does not. It does include Regular and Contract as the ProductivityVCC report does.

Resolution: Corrected by adding column to act hours, act dollars, bud hours, bud dollars to account for PayType.PayDetail=Other. We then added these new columns to the calculation of actual and target productivity index and labor rate. This now matches the ProductivityVCC report.

Symptom: The current formula in cell T6 is using the OR argument comparing YTD Actuals and CM Target. So where there is YTD data but no data for CM Target, the argument is true thereby making the YTD Index calc zero when it should be using the YTD values. The AND argument will correct this logic error.

Resolution: Updated the formula in cell T6 to use the AND argument.

Symptom: The productivity index graph in the ProductivityVCC report shows inaccurate results.

Resolution: Corrected by updating the formula in cells L13:Q13 to reference correct columns.

Issue: In 2019.4, the logic to show the proper pay type for Global Benefit lines on the PComp tab is broken.

Resolution: Corrected by updating the filter logic in AQ9.

Issue: The Expense sheet references a blank cell in the StatRev sheet, so the default label will always display Calendar Days.

Resolution: Corrected by updating the Expense sheet to reference the total statistics line on the StatRev sheet.

Issue: In 2019.3, AQ1 on the ProviderSummary sheet is hardcoded incorrectly, so the system does not bring in the current settings from the Provider Config Driver.

Resolution: When separating the PFP_template Provider from the Master template, ProviderSummary!AQ1 was accidentally deactivated. Corrected by activating the AQ.

Issue: When adding a new job code block to a plan file, the user saves, closes, and upon re-opening the plan file, the new job code is listed twice.

Resolution: When adding a duplicate job code, the system will display the duplicate in the sheet. The duplicate does not save to the database, however, to remove it from the sheet you will need to either rebuild the plan file or manually delete it from the sheet.

Issue: In 2019.3, the lookup logic was modified to pull in GL account information into the JobCode sheet. If you do not use the PayrollGLMapping table or Paytype table for GL Accounts, when manually adding a job code on the JobCode sheet or setting the NYBKHA column in the ActPay27 table to 1 to interface in, the system does not bring in the account from the JobCode table. As a result, dollars and hours cannot flow to the Expense sheet.

Resolution: Corrected by requiring new JobCode blocks to include similar field values as the interfaced JobCode blocks.

Issue: The logic error is that the value of the denominator for (PTO Alloc % for YTD) occurs at the beginning of the formula when the division calculation is 4 embedded IF-THEN conditions. If the denominator is zero, but the logic doesn't result in the associated calculation, then the result is erroneously zero and will not recognize any PTO configurations from the Labor Override driver.

Resolution: Corrected by updating the test for IF YTD PTO=0 to the correct section of the formula.

Issue: In the Staffing tab, the total hours for each month on row 1 of the Add New Jobcode calc method includes misplaced parentheses that causes the calculated hours for each pay type row to be less than they should be. This impacts variable job codes, as the total hours for the budget months do not tie to the annual paid hours section of each job code for the Staffing tab. This leads to understated hours on the Expense tab.

Resolution: In the Add New JobCode Block calc method, corrected by adjusting the formula so that the FTEs by row are no longer being reduced by the formula error within the Hours columns.

Issue: In 2019.3 when adding new providers to a plan file, users can no longer adjust Gross Revenue (no blue input cell) for the current year in the Manager Input column, which was an available functionality several versions ago. The logic in this field is similar to the Existing Provider "Manager Input logic" where it uses the (YTD Gross Charges / YTD of the chosen driver from the drop-down) multiplies by the Manager Input value of the chosen driver to calculate your gross charge adjustment. While this works in one case for existing providers, its problematic as it does not allow for additional adjustment on existing or new providers (they have zero YTD).

Resolution: Corrected the Revenue calc method by reinstating the same update as the original calc methods. Changes were made to the cells in columns L, FK, and GE for the one line Revenue calc method.

Issue: In the budget workbook, users can include the current year's budget detail information from the last completed budget. In the 2019.3 version, however, the system brings over the budget amounts but not the description for RedcordIDDesc and BudComments.

Resolution: Corrected by adding fields for the two prior years (RecordIDDesc and BudComments) in the field definition row so that the text comments entered from prior budget year will be included in the plan file.

Issue: With the changes in 2019.3 for plan files, an issue occurs when you roll up job codes together using the KHABGTcode column. The system picks up the description of one of the higher-numbered job codes since now the system uses an AQ to bring in that data versus a using GetData.

Resolution: Corrected by updating the formula for the description to use Jobcode.KHABgtCode.Description.

Issue: With the separation of the ProviderDetail and ProviderSummary logic (starting in 2019.3), the stat referencing formulas on the first line of the PComp block on the ProviderComp tab for CYB, YTD, and Remaining Projected were not correctly updated . If you elect to use a stat that is not the driving stat, the system correctly picks it up for the budget months, but not CYB, YTD , and Remaining Projected.

Resolution: Corrected by adjusting the calc method library formulas to identify driver setup for ProviderDetail or ProviderSummary and bring in the correct stat value if configured for a non-driver statistic. (ie...WRVUs may be the driver stat but provider comp is using Encounters).

Issue: In cell T87 on Expense tab, the description defaults to Calendar Days because of a cell formula error that looks to a blank cell on the Stat_Rev tab.

Resolution: Corrected by updating the formula so that "Total Key Statistic" is the description when dept statistics are used.

Issue: The provider driver department description is len(50), while the department dimension description is len(100). The difference causes a save error and prevents the driver from saving.

Resolution: Corrected by increasing the description field length to 100.

Issue: The definition in Column S is Dept.KHABgtMap.Description, but row 46 description displays "Account Description" when it should display "Description".

Resolution: Corrected by changing "Account Description" to "Description".

Issue: When bringing in the YTD volume for the provider, the Field Definition Row is set to pull in YTD and this can change during the budget process if a second save needs to occur due to volume changes. Cell N27 should be referencing the Variables tab and cell 11 for the Budget YTD period.

Resolution: When bringing in the YTD volume for the provider, the Field Definition Row is set to pull in YTD and this can change during the budget process if a second save needs to occur due to volume changes. Cell N27 now references the Variables tab and cell 11 for the Budget YTD period. This replaces using the system YTD, which can change over the budget period. The second issue was corrected for the Rem Volume column by deleting the formula in O25 so that is uses the calculation in O26 instead, which takes the total minus YTD to get the projection versus using ProjCalc.

Issue: The new data validation for Jobcode and Paytype do not allow for NotUsed as an option unless the driver is unprotected and it is manually entered. This is problematic because all the Hide Row logic in the plan file is looking for NotUsed in the Paytype column to determine if a row should be Visible or Hidden. This would not be an issue for a client with a rolled over File Group that had NotUsed previously listed, but any new system or changes would be problematic as most clients do not have the access to overwrite like that.

Resolution: Corrected by updating the picklists to select 'NotUsed' as an option.

Issue: The new format of Labor Config no longer includes a description for the Lump Sum Payout line. As a result, it now displays in the plan file with a zero instead of a description.

Resolution: Corrected by updating cell Q83 to be an input cell in the Labor Configuration tab.

Issue: The logic to default a newly inserted budget group to the values in Globals was missing. This was a feature added in 2019.3, which included cell logic that if the current budget group row was blank, then use Global. The driver was inadvertently reverted to a version where this logic was not present.

Resolution: Corrected by restoring the driver and calc method library from the correct version.

Issue: The Budget Labor Configuration driver does not correctly calculate the Global Hours in Period when Global Hours Per FTE Convention is "Default" AND the year is a Leap Year.

Resolution: Corrected by updating multiple formulas in the Budget Labor Configuration driver and the Budget Group calc method.

Issue: When refreshing the Budget Statistics Driver, AQ9 displays a warning that the number of records retrieved exceeds the maximum allowed, even when only one budget group was selected.

Resolution: Corrected by adjusting the data filter for AQ9 to consider the budget group selected in the refresh variable.

Issue: The system displays a data conversion error when refreshing the Budget Statistics driver (AQ9) due to large decimal values in the ChgPct column in the table.

Resolution: Corrected by updating the formula in column AS to include round logic out to 10 decimals. This will prevent the large integer errors caused by division issues of very small values.

Issue: The system does not save all changes to the Provider List.

Resolution: Corrected by updating the save logic on the affected rows.

Issue: With the move to 2019.3, the Paytype field for the Prod and Non Prod categories in the Labor Config Driver changed from an input field to a GetDataElement. The issue that shows up is the description for the paytype listed may belong a child pay type of it.

Resolution: Corrected by concatenating PAYTYPE.JobCode=PAYTYPE.Paytype to all four data sources ,which now brings in the correct description for the category.

Issue: When using CYB_FTE as the comparison on Jobcode, the new summary section of the Jobcode does not contain the logic to include CYB_FTE.

Resolution: After identifying that column K calculated "FTE Comparison to YTD", which was derived from the Labor Config driver setting for JobCode and Employee labor tabs, corrected by adjusting the sum logic in total sections for JobCode summary blocks to identify values in details section. The Summary sections utilize logic to retrieve sums of details instead of retrieving values directly from the table with the setup of Axiom UDF ReapplyCurrentViews.

Issue: On the Staffing tab, when employing the use of KHABGTMap depts, dollar only pay types on these job code blocks may not show the correct dept (shows KHABGTCode Dept), thus preventing the flow of the data to the Expense tab if the account happens to be different than the home department. This may create an out of balance condition in the workbook between the Expense and Staffing tab as well as inaccurate financial reporting.

Resolution: Corrected by adjusting the calc method library formulas in columns CK and CL to look to the dept used for that specific job code versus the host department.

Issues:

1. On the Jobcode and ProviderComp tabs, YTD Retirement Calculation for salary for the prior year was understated due to not including the per/FTE component.
2. On the Jobcode and ProviderComp tabs, the retirement calculation for month 1 of the calendar year may not calculate due to comparing erroneously to YTD of prior year.

Resolution:

1. In the Jobcode and Provider calc methods, corrected by updating the formulas in column BG and LineRef Jobcode 29 to divide by the FTEs to convert the formula to salary per FTE.
2. In the Jobcode and Provider calc methods, corrected by adding logic to test for month 1 and apply the retirement limits to just that month.

Issue: In 2019.3, the Contract Provider lines of the Provider template > ProviderComp tab do not flow to the Expense tab for the corresponding dollars and hours accounts listed in columns CK and CL. These lines flow to the ProviderCompData tab for account zero, which is not picked up on the Expense tab.

Resolution: Corrected by implementing logic in the Provider template > ProviderComp tab similar to other sections in order to flow data from the static Contract Provider line items to the Expense tab for Acct and HrsAcct.

Issue: When moving to 2019.3, we changed the formula logic in the Projected Dollars column of the Jobcode Block on the Jobcode tab to factor in the rate for FTEs that are Program Additions or Position Changes. There is a missing component in this formula on the OT row which could lead to understating projected dollars if you add FTEs or overstate if you remove FTEs because the rate used does not factor in the 1.5 times the rate used on the Program Addition and Position Change lines.

Resolution: Corrected so that when removing FTEs from the productive and non-productive rows, each row will refer to it's own rate. For example, if backing out 1.0 FTEs and rows like overtime were used, the dollars will be backed out at the overtime rate as defined by the percentage used in column F of the jobcode block for that row. This update will include any rates used in the Program Additions and Program Changes rows.

Issue: The PayrollGLMapping does not work correctly due to an formula issue in column DK.

Resolution: Corrected by updating the formulas for row range extension to 2500 (alleviating concerns of clients with workbooks that exceed this row count, causing the formula to once again break) in the Employee calc method.

Issue: When adding a new job code block to a plan file, the user saves, closes, and upon re-opening the plan file, the new job code is listed twice. The issue was related to the new job code being added below the insert new job code command row. The newly added job code erroneously became a persistent part of the tab.

Resolution: By moving the insert command to the same row and the insert prompt, this updated the range so that newly added job codes were within the rebuildable part of the labor tab.

Issue: With the changes in 2019.3, we modified our lookup logic for pulling GL account information on the Jobcode tab. If clients do not use the PayrollGLMapping table or Paytype table for GL Accounts, when a job code is added manually on the Jobcode tab or by setting the NYBKHA in the ActPay27 table to 1 to interface in, it does not bring in the account from the Jobcode table, thus dollars and hours cannot flow to the Expense tab.

Resolution: Corrected by incorporated RelatedColumnValues for the JobCode block calc method to populate the additional required fields. These column values are now resident in the Jobcode calc method so that the lookup to the Jobcode.StdHours, Jobcode.HRAcct and Jobcode.GLAcct are established.

Issue: With the changes made in moving to 2019.3, AQ1 on the ProviderSummary tab is hardcoded to Off. As a result, the system will not bring in the current settings from the Provider Config Driver.

Resolution: Corrected by replicating ProviderDetail!AQ1 in ProviderSummary!AQ1 since they used the same parameters.

Issue: On the Stat_Rev tab when using calc methods that allow for a spread option of History, the spread logic in the monthly values refers to the Configuration tab of the Master template. However, when using spread logic, we always need to use DB fields from the prior year when the periods are past the period used for current year. (For example, if YTD is thru period 8, the periods 9-12 should be prior year).

Resolution: Updated the formulas on the Configuration tab to resolve to the prior period table.

Issue: In 2019.4, the logic to show the proper pay type for Global Benefit lines on the ProviderComp tab may not return the expected paytpes due to a missing parameter in one of the AQs.

Resolution: Corrected by updating the related AQ on the Drivers tab of the plan file.

Issue: When using Global Benefit rows from the Provider List driver, the values would not spread correctly in rows 2 thru 5 of global benefit rows on the ProviderComp tab. The issue was related to a range reference that was not locked to the needed row.

Resolution: Corrected by making formula update to rows two thru five of the global benefit section.

Issue: When working on the ProviderComp tab and everything is in summary view, no totals for the FTEs or hours display - even after saving.

Resolution: Corrected by doing the following:

• Mirrored the "Total" tags identified in column CP in new column location (column CJ) for the template and Provider block calc methods.
• Adjusted the logic for Hours, FTEs, and FICA to reference column CJ instead of column CP to display on the summary section of the calc method.

Issue: A user adds a new provider to the PROVIDER table and drivers, and then inserts the Add New ProviderSummary calc method on ProviderSummary for the provider. The user then adds data and saves the plan file. The next time the plan file is opened, the provider block displays twice, resulting in recalculation errors due to duplicate keys.

Resolution: ProviderSummary uses a single calc methods for interfaced and add new. Persistent plan file requires key for add new once the record is saved to the db. If key does not exist, insert/update AQ will populate record found in table and persistent record with other save rows produce a save error. Incorporated formula to derive keys for AQ7.

Issue: After adding a new employee, a recalculation may cause a duplicate occurrence of the added employee block.

Resolution: Corrected by adding the needed rebuild keys to the Add New Employee calc method to prevent the system from creating a duplicate.

Issue: An error message displays indicating a duplicate row may display below the "Click here to insert new paytype" section of the job code. This may occur when using the third Non-Prodcutive row of the Employee calc method.

Resolution: Corrected the formula that references the rebuild key needed on the affected row.

Issue: If changing the calc method in the Dept Other Pay section of the Jobcode tab, the changed calc methods reverts to original calc method upon rebuild of a plan file.

Resolution: Identified that the change calc method rules require a value or formula in a related cell to mitigate a replacement calc method with an empty cell. An empty cell from the replacement calc method will override the former. Corrected by incorporating formulas to derive key values in each dollars AQ.

Issue: When using the Fixed Adjustment rows of the self balancing option, the row reference was incorrect resulting in the adjustment not being recognized.

Resolution: This issues resolved when resolving an issue with Add New jobcode which corrected the last row range reference.

Issue: The Patient Revenue calc method may duplicate a row after recalc. The issue is due to a formula error in the rebuild key column for this calc method.

Resolution: Corrected by updating the rebuild logic on row 3 of the calc method.

Issue: When a user is a member of Budget Hide Salaries and navigates to the Jobcode sheet, the start rates are hidden when scrolling to column BU and BV.

Resolution: Corrected by implementing the proposed [hidecolumn] logic similar to formulas identified in column G for multiple views. Determination for role based views located on Variables tab.

Issue: In Budget Deductions utility (Reports Library\Budgeting Utilities\Balance Sheet & Deductions\Budget Deductions), on the Total Patient Days - Globals row (row 116 in template), there is a formula reference to the incorrect month in the monthly columns AL:AW.

Resolution: Corrected the formula Budget_Deductions!AL116 and copied it to Budget_Deductions!AM116:AW116.